package org.yonggan.shop.sql.report

import org.apache.spark.sql.DataFrame
import org.yonggan.shop.constant.ConfigurationManager
import org.yonggan.shop.utils.SparkUtils

/**
  * 各个分类（手机、书）商品页面被浏览次数最多的TON2
  */
object RptGroupedTopNSQL {

  def main(args: Array[String]): Unit = {

    val spark = SparkUtils.getSparkSession("商品页面被浏览次数最多的TON2")

    spark.sparkContext.setLogLevel("WARN")

    // 文件输出
    val jsonDF: DataFrame = spark.read.json(ConfigurationManager.TASK_INJSON)
    import org.apache.spark.sql.functions._

    val bsDF: DataFrame = jsonDF.filter(
      """
        | event_type = 1
        | and cid is not null
        | and pid is not null
        | and page = "/pages/content/detail/detail"
      """.stripMargin) //
      .groupBy("cid","pid").agg(count("*") as "ctn")
      .select("cid","pid", "ctn")

    bsDF.createTempView("v_data")
    // 分组topN
//    val resDF = spark.sql(
//      """
//        |SELECT cid,pid, ctn,ROW_NUMBER() OVER (PARTITION BY cid ORDER BY ctn) as rn
//        |FROM v_data
//      """.stripMargin)
    /*
        使用ROW_NUMBER 注意的并列排名 ROW_NUMBER 是按照行号提取数据
     */
    spark.sql(
      """
        |SELECT cid,pid, ctn,RANK() OVER (PARTITION BY cid ORDER BY ctn desc) as rn
        |FROM v_data
      """.stripMargin)
      .show()

    spark.stop
  }

}
